# 1、合并收集上来的xx年xx月收集数据.xlsx
# 2、计算每个员工当月的绩效系数，存为总表xx年xx月员工绩效系数.xlsx
# 3、生成一个项目明细表，连同横道图放到同一个html中
# 3、获取项目名称和项目实际进度，绘制横道图，存到本地xx年xx月项目进度.html

#旧表

# month_evaluate.py
import os
import pandas as pd
from datetime import datetime
import plotly.express as px

# ======== 1. 配置 ========
INIT_DATA_PATH = "E:/工作临时文件/Evaluate_data/init_data"
MONTH_EVALUATE_PATH = "E:/工作临时文件/Evaluate_data/Month_data/month_workers_evaluate"
MONTH_PROJECT_RESULT_PATH = "E:/工作临时文件/Evaluate_data/Month_data/month_project_result"

# 项目类型难度系数 (文档表2)
PROJECT_DIFFICULTY = {
    "项目后评价类": 1.00,
    "投资总体评价类": 1.34,
    "项目及企业管理咨询类": 2.11,
    "企业投资规划与战略规划": 2.62,
    "投资、并购及资产重组类": 1.57,
    "能源经济政策与商业模式类": 3.14,
    "低碳技术经济咨询类": 3.31,
    "电力市场改革研究类": 4.24,
    "配合其他部门单个项目财务测算": 0.2,
    "前期项目策划：项目建议书": 0.15,
    "前期项目策划：项目可研或投标": 0.18,
    "前期项目策划：一整套流程（建议书、可研、PPT等）":0.2
}

# # 角色系数
# ROLE_COEF = {
#     "项目主设人": 1.2,
#     "编制人": 1.1,
#     "校核人": 1.0
# }
#
# # 工作量比例
# WORKLOAD_RATIO = {
#     "项目主设人": 0.4,
#     "编制人": 0.3,
#     "校核人": 0.1
# }


def get_current_month_filename():
    """获取当前年月的文件名"""
    now = datetime.now()
    year = f"{now.year}年"
    month = f"{now.month:02d}月"
    return year + month + "收集数据.xlsx", year + month

def merge_sheets(file_path):
    """读取Excel文件前14个sheet，合并为一个DataFrame"""
    xls = pd.ExcelFile(file_path)
    all_sheets = xls.sheet_names[:14]
    df_list = [pd.read_excel(xls, sheet_name=s) for s in all_sheets]
    merged_df = pd.concat(df_list, ignore_index=True)
    return merged_df

# def calc_employee_performance(df):
#     """
#     根据合并后的DataFrame计算每个人绩效得分
#     df字段包含：
#         - 员工列（如蔡康康、李诗琪…）
#     """
#     result = {}
#     for idx, row in df.iterrows():
#         project_type = str(row.get("项目类型", ""))
#         difficulty = PROJECT_DIFFICULTY.get(project_type, 1.0)
#
#         # 获取实际进度（百分比 -> 小数）
#         progress = str(row.get("实际进度", "0")).replace("%", "")
#         try:
#             progress_ratio = float(progress) / 100.0
#         except:
#             progress_ratio = 0.0
#
#         # 参与人员（主设、校核、参与）
#         main_person = str(row.get("项目主设人", "")).strip()
#         checker = str(row.get("校核人", "")).strip()
#         participants = str(row.get("项目参与人", "")).split("，") if pd.notna(row.get("项目参与人")) else []
#
#         # 计算每个角色绩效
#         if main_person:
#             result[main_person] = result.get(main_person, 0) + (
#                 difficulty * progress_ratio *
#                 WORKLOAD_RATIO["项目主设人"] * ROLE_COEF["项目主设人"]
#             )
#
#         if checker:
#             result[checker] = result.get(checker, 0) + (
#                 difficulty * progress_ratio *
#                 WORKLOAD_RATIO["校核人"] * ROLE_COEF["校核人"]
#             )
#
#         for p in participants:
#             p = p.strip()
#             if p:
#                 # 默认编制人
#                 result[p] = result.get(p, 0) + (
#                     difficulty * progress_ratio *
#                     WORKLOAD_RATIO["编制人"] * ROLE_COEF["编制人"]
#                 )
#
#     # 转换为DataFrame
#     df_res = pd.DataFrame(result.items(), columns=["员工", "月度绩效系数"])
#     return df_res


def calc_employee_score(df):
    """只计算参与人列员工的月度绩效得分"""
    # 找到员工列起始（第一个叫‘蔡康康’的列）
    start_idx = df.columns.get_loc("蔡康康")
    emp_cols = df.columns[start_idx:]  # 所有员工列

    result = {}

    for idx, row in df.iterrows():
        proj_type = str(row.get("项目类型", "")).strip()
        difficulty = PROJECT_DIFFICULTY.get(proj_type, 1.0)  # 找不到默认1.0

        for emp in emp_cols:
            try:
                val = float(row[emp]) if pd.notna(row[emp]) else 0.0
            except:
                val = 0.0
            # 员工该项目绩效 = 员工列值 * 项目难度
            score = val * difficulty
            result[emp] = result.get(emp, 0) + score

    return pd.DataFrame(result.items(), columns=["员工", "月度绩效得分"])


def save_performance_result(df_res, month_str):
    """保存员工绩效系数表格"""
    save_path = os.path.join(MONTH_EVALUATE_PATH, f"{month_str}员工绩效系数.xlsx")
    os.makedirs(MONTH_EVALUATE_PATH, exist_ok=True)
    df_res.to_excel(save_path, index=False)
    print(f"✅ 员工绩效系数表已保存: {save_path}")


def draw_project_progress(df, month_str):
    """绘制项目进度横道图并生成HTML"""
    df_plot = df[["项目名称", "实际进度"]].copy()
    df_plot["实际进度"] = df_plot["实际进度"].astype(str).str.replace("%", "").astype(float)
    fig = px.bar(df_plot,
                 x="实际进度",
                 y="项目名称",
                 orientation="h",
                 color="实际进度",
                 color_continuous_scale="Greens",
                 title=f"{month_str} 项目进度横道图")

    html_path = os.path.join(MONTH_PROJECT_RESULT_PATH, f"{month_str}项目进度.html")
    os.makedirs(MONTH_PROJECT_RESULT_PATH, exist_ok=True)
    fig.write_html(html_path)
    print(f"✅ 项目进度横道图已生成: {html_path}")

def main():
    # 当前月份文件名
    file_name, month_str = get_current_month_filename()
    file_path = os.path.join(INIT_DATA_PATH, file_name)

    if not os.path.exists(file_path):
        print(f"❌ 本月文件不存在: {file_path}")
        return

    print(f"📂 读取文件: {file_path}")
    merged_df = merge_sheets(file_path)

    # ===== 任务1：计算绩效系数 =====
    perf_df = calc_employee_score(merged_df)
    save_performance_result(perf_df, month_str)

    # ===== 任务2：绘制项目进度横道图 =====
    draw_project_progress(merged_df, month_str)


if __name__ == "__main__":
    main()
